﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.Models;
using SWDataDB.DAL;

namespace SWDataDB.BLL
{
    public class KCAdvanceQuery
    {
        public KCAdvanceQuery() { }

        #region ===以著录表的档号为外键，查找相关的表,返回DataSet===
        /// <summary>
        /// 钻孔查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet YXAdvancedQuery(string file_id)
        {
            string strWhere;
            if (string.IsNullOrEmpty(file_id))
            {
                strWhere = "";
            }

            else
            {
                strWhere = "ZL_FILE_ID='" + file_id + "'";
            }

            YXInfo yx_info = new YXInfo();

            return yx_info.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 其它实物查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet UTILAdvancedQuery(string file_id)
        {
            string strWhere;
            if (string.IsNullOrEmpty(file_id))
            {
                strWhere = "";
            }

            else
            {
                strWhere = "ZL_FILE_ID='" + file_id + "'";
            }

            UTILInfo info = new UTILInfo();

            return info.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 相关文件查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet XGAdvancedQuery(string file_id)
        {
            string strWhere;
            if (string.IsNullOrEmpty(file_id))
            {
                strWhere = "";
            }

            else
            {
                strWhere = "ZL_FILE_ID='" + file_id + "'";
            }

            XGInfo info = new XGInfo();

            return info.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 标本表查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet BBAdvancedQuery(string file_id)
        {
            string strWhere;
            if (string.IsNullOrEmpty(file_id))
            {
                strWhere = "";
            }

            else
            {
                strWhere = "ZL_FILE_ID='" + file_id + "'";
            }

            BBInfo bbInfo = new BBInfo();

            return bbInfo.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 薄片查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet BPAdvancedQuery(string file_id)
        {
            string strWhere;
            if (string.IsNullOrEmpty(file_id))
            {
                strWhere = "";
            }

            else
            {
                strWhere = "ZL_FILE_ID='" + file_id + "'";
            }

            BPInfo bpinfo = new BPInfo();
            return bpinfo.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 光片查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet GPAdvancedQuery(string file_id)
        {
            string strWhere;
            if (string.IsNullOrEmpty(file_id))
            {
                strWhere = "";
            }

            else
            {
                strWhere = "ZL_FILE_ID='" + file_id + "'";
            }

            GPInfo gpinfo = new GPInfo();
            return gpinfo.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 样品查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet YPAdvancedQuery(string file_id)
        {
            string strWhere;
            if (string.IsNullOrEmpty(file_id))
            {
                strWhere = "";
            }

            else
            {
                strWhere = "ZL_FILE_ID='" + file_id + "'";
            }
            YPInfo ypinfo = new YPInfo();
            return ypinfo.GetInfoForPageByASC(strWhere);
        }

        #endregion

        #region ===以关键字段，查找相关的表，返回dataset===
        /// <summary>
        /// 钻孔查找
        /// </summary>
        /// <param name="file_id">档号</param>
        /// <param name="title">案卷题名</param>
        /// <param name="YX_CODE">钻孔编号</param>
        /// <param name="KT_ID">勘探线编号</param>
        /// <param name="start">开孔日期</param>
        /// <param name="end">终孔日期</param>
        /// <returns></returns>
        public DataSet YXAdvancedQuery(string title, string file_id, string YX_CODE, string KT_ID, string start, string end)//strTitle, strFileID, strZKCODE, strKTLCODE, strtxtPKIIHStart, strtxtPKIIHEnd
        {
            //根据其余字段得到

            YXInfo yx_info = new YXInfo();
            string strWhere = string.Empty;
            strWhere += " 1=1 AND ";
            if (!string.IsNullOrEmpty(YX_CODE))
                strWhere += "ZK_CODE like '%" + YX_CODE + "%' AND ";
            if (!string.IsNullOrEmpty(KT_ID))
                strWhere += "KT_LCODE like '%" + KT_ID + "%' AND ";
            if (!string.IsNullOrEmpty(start))
                strWhere += "START_DATE >= '" + start + "' AND ";
            if (!string.IsNullOrEmpty(end))
                strWhere += "END_DATE <= '" + end + "' AND ";
            if (!string.IsNullOrEmpty(file_id))
                strWhere += "ZL_FILE_ID like '%" + file_id + "%' AND ";
            if (!string.IsNullOrEmpty(title))
                strWhere += "ZL_FILE_ID IN (SELECT FILE_ID FROM ZL_TBL WHERE (TITLE like '%" + title + "%')) AND ";
            
            strWhere = strWhere.Remove(strWhere.Length - 5, 4);
            return yx_info.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 其它实物查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <param name="type"></param>
        /// <param name="cj_name"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public DataSet UTILAdvancedQuery(string file_id,string type,string cj_name,string date)
        {
            string strWhere = string.Empty;
            strWhere += " 1=1 AND ";
            if (!string.IsNullOrEmpty(file_id))
                strWhere += "ZL_FILE_ID like '%" + file_id + "%' AND ";
            if (!string.IsNullOrEmpty(type))
                strWhere += "SW_TYPE like '%" + type + "%' AND ";
            if (!string.IsNullOrEmpty(cj_name))
                strWhere += "COL_NAME like '%" + cj_name + "%' AND ";
            if (!string.IsNullOrEmpty(date))
                strWhere += "COL_DATE='" + date + "' AND ";
            UTILInfo info = new UTILInfo();

            strWhere = strWhere.Remove(strWhere.Length - 5, 4);
            return info.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 相关文件查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <param name="code"></param>
        /// <param name="style"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataSet XGAdvancedQuery(string file_id,string code,string style,string name)
        {
            string strWhere = string.Empty;
            strWhere += " 1=1 AND ";
            
            if (!string.IsNullOrEmpty(code))
                strWhere += "XG_CODE like '%" + code + "%' AND ";
            if (!string.IsNullOrEmpty(style))
                strWhere += "DATA_STYLE like '%" + style + "%' AND ";
            if (!string.IsNullOrEmpty(name))
                strWhere += "DATA_NAME like '%" + name + "%' AND ";

            //if (!string.IsNullOrEmpty(title))
            //    strWhere += "ZL_FILE_ID IN (SELECT FILE_ID FROM ZL_TBL WHERE (TITLE like '%" + title + "%')) AND ";
            if (!string.IsNullOrEmpty(file_id))
                strWhere += "ZL_FILE_ID like '%" + file_id + "%' AND ";
            XGInfo info = new XGInfo();

            strWhere = strWhere.Remove(strWhere.Length - 5, 4);
            return info.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 标本表查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <param name="title"></param>
        /// <param name="bb_id"></param>
        /// <param name="bb_name"></param>
        /// <param name="pos"></param>
        /// <param name="way"></param>
        /// <param name="out_name"></param>
        /// <param name="in_name"></param>
        /// <returns></returns>
        public DataSet BBAdvancedQuery(string file_id, string title, string bb_id, string bb_name, string pos, string way,
            string out_name,string in_name)
        //strfileID, strtitleName, strBBCode, strBBName, strsamLoction, strsamMethod, stroutName, strinName
        {
            string strWhere = string.Empty;
            strWhere += " 1=1 AND ";

            if (!string.IsNullOrEmpty(bb_id))
                strWhere += "BB_CODE like '%" + bb_id + "%' AND ";
            if (!string.IsNullOrEmpty(bb_name))
                strWhere += "BB_NAME like '%" + bb_name + "%' AND ";
            if (!string.IsNullOrEmpty(pos))
                strWhere += "CY_POS like '%" + pos + "%' AND ";
            if (!string.IsNullOrEmpty(way))
                strWhere += "QY_METHOD like '%" + way + "%' AND ";
            if (!string.IsNullOrEmpty(out_name))
                strWhere += "OUT_NAME like '%" + out_name + "%' AND ";
            if (!string.IsNullOrEmpty(in_name))
                strWhere += "INNAR_NAME like '%" + in_name + "%' AND ";
            if (!string.IsNullOrEmpty(file_id))
                strWhere += "ZL_FILE_ID like '%" + file_id + "%' AND ";
            if (!string.IsNullOrEmpty(title))
                strWhere += "ZL_FILE_ID IN (SELECT FILE_ID FROM ZL_TBL WHERE (TITLE like '%" + title + "%')) AND ";
            BBInfo bbInfo = new BBInfo();

            strWhere = strWhere.Remove(strWhere.Length - 5, 4);

            return bbInfo.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 薄片查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <param name="title"></param>
        /// <param name="bp_code"></param>
        /// <param name="bp_name"></param>
        /// <param name="pm_name"></param>
        /// <param name="pm_code"></param>
        /// <returns></returns>
        public DataSet BPAdvancedQuery(string file_id,string title,string bp_code,string bp_name,string pm_code,string pm_name)
        {//strfileID, strfileID, strBPCode, strBPName, strPMPATHName, strPMPATHCode
            string strWhere = string.Empty;
            strWhere += " 1=1 AND ";
            if (!string.IsNullOrEmpty(bp_code))
                strWhere += "BP_CODE like '%" + bp_code + "%' AND ";
            if (!string.IsNullOrEmpty(bp_name))
                strWhere += "BP_NAME like '%" + bp_name + "%' AND ";
            if (!string.IsNullOrEmpty(pm_name))
                strWhere += "PM_PATH_NAME like '%" + pm_name + "%' AND ";
            if (!string.IsNullOrEmpty(pm_code))
                strWhere += "PM_PATH_NUM like '%" + pm_code + "%' AND ";
            if (!string.IsNullOrEmpty(file_id))
                strWhere += "ZL_FILE_ID like '%" + file_id + "%' AND ";
            if (!string.IsNullOrEmpty(title))
                strWhere += "ZL_FILE_ID IN (SELECT FILE_ID FROM ZL_TBL WHERE (TITLE like '%" + title + "%')) AND ";
            BPInfo bpinfo = new BPInfo();

            strWhere = strWhere.Remove(strWhere.Length - 5, 4);
            return bpinfo.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 光片查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet GPAdvancedQuery(string file_id, string title, string gp_code, string gp_name, string pm_code, string pm_name)
        {
            string strWhere = string.Empty;
            strWhere += " 1=1 AND ";
            if (!string.IsNullOrEmpty(gp_code))
                strWhere += "GP_CODE like '%" + gp_code + "%' AND ";
            if (!string.IsNullOrEmpty(gp_name))
                strWhere += "GP_NAME like '%" + gp_name + "%' AND ";
            if (!string.IsNullOrEmpty(pm_name))
                strWhere += "PM_PATH_NAME like '%" + pm_name + "%' AND ";
            if (!string.IsNullOrEmpty(pm_code))
                strWhere += "PM_PATH_NUM like '%" + pm_code + "%' AND ";
            if (!string.IsNullOrEmpty(file_id))
                strWhere += "ZL_FILE_ID like '%" + file_id + "%' AND ";
            if (!string.IsNullOrEmpty(title))
                strWhere += "ZL_FILE_ID IN (SELECT FILE_ID FROM ZL_TBL WHERE (TITLE like '%" + title + "%')) AND ";
            GPInfo gpinfo = new GPInfo();

            strWhere = strWhere.Remove(strWhere.Length - 5, 4);
            return gpinfo.GetInfoForPageByASC(strWhere);
        }

        /// <summary>
        /// 样品查找
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet YPAdvancedQuery(string file_id,string title,string yp_code,string yp_name,string pm_code,string pm_name, string out_name,string in_name)
        {//strfileID, strtitleName, strYPCODE, strYPName, strPMPATHName, strPMPATHCode, stroutName, strinName
            string strWhere = string.Empty;
            strWhere += " 1=1 AND ";

            if (!string.IsNullOrEmpty(yp_code))
                strWhere += "YP_CODE like '%" + yp_code + "%' AND ";
            if (!string.IsNullOrEmpty(yp_name))
                strWhere += "YP_NAME like '%" + yp_name + "%' AND ";
            if (!string.IsNullOrEmpty(pm_name))
                strWhere += "PM_PATH_NAME like '%" + pm_name + "%' AND ";
            if (!string.IsNullOrEmpty(pm_code))
                strWhere += "PM_PATH_NUM like '%" + pm_code + "%' AND ";
            if (!string.IsNullOrEmpty(out_name))
                strWhere += "OUT_NAME like '%" + out_name + "%' AND ";
            if (!string.IsNullOrEmpty(in_name))
                strWhere += "INNAR_NAME like '%" + in_name + "%' AND ";
            if (!string.IsNullOrEmpty(file_id))
                strWhere += "ZL_FILE_ID like '%" + file_id + "%' AND ";
            if (!string.IsNullOrEmpty(title))
                strWhere += "ZL_FILE_ID IN (SELECT FILE_ID FROM ZL_TBL WHERE (TITLE like '%" + title + "%')) AND ";
            YPInfo ypinfo = new YPInfo();

            strWhere = strWhere.Remove(strWhere.Length - 5, 4);
            return ypinfo.GetInfoForPageByASC(strWhere);
        }




        public DataSet YXDescriQuery(string ZK_CODE,string ZL_FILE_ID,string TITLE)
        {
            string strWhere = string.Empty;
            strWhere += " 1=1 AND ";

            if (!string.IsNullOrEmpty(ZK_CODE))
                strWhere += "ZK_CODE like '%" + ZK_CODE + "%' AND ";
            if (!string.IsNullOrEmpty(ZL_FILE_ID))
                strWhere += "ZL_FILE_ID like '%" + ZL_FILE_ID + "%' AND ";

            if (!string.IsNullOrEmpty(TITLE))
                strWhere += "ZL_FILE_ID IN (SELECT FILE_ID FROM ZL_TBL WHERE (TITLE like '%" + TITLE + "%')) AND ";
            YXDeepthInfo yxinfo = new YXDeepthInfo();

            strWhere = strWhere.Remove(strWhere.Length - 5, 4);
            return yxinfo.GetInfoForPageByDESC(strWhere);
        }

        #endregion


        #region 钻孔图片查找

        /// <summary>
        /// 钻孔图片查找
        /// 根据档号、钻孔编号和回次查找该回次下的所有图片
        /// 返回图片的名称
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet ZKImgAdvancedQuery(string zl_file_id, string zk_code, int hc)
        {
            ZKImageInfo ypinfo = new ZKImageInfo();
            return ypinfo.GetPicNameWithSet(zl_file_id, zk_code, hc);
        }

        /// <summary>
        /// 根据档号和钻孔编号得到各个回次深度
        /// </summary>
        /// <param name="zl_file_id"></param>
        /// <param name="zk_code"></param>
        /// <returns></returns>
        public DataSet ZKImgHCDeep(string zl_file_id,string zk_code)
        {
            int nHC=-1;
            int nCount=-1;
            DataSet ds = new DataSet();

            ZKImageInfo info = new ZKImageInfo();
            ds = info.GetAllPicWithSet(zl_file_id, zk_code);
            nCount = info.GetZKImageInfoCount(zl_file_id, zk_code);

            nHC = int.Parse(ds.Tables[0].Rows[0][6].ToString());

            string[] HCDeep1 = new string[nHC];
            string[] HCDeep2 = new string[nHC];
            string[] HCDeep = new string[nHC];

            //int j=0;
            int i=0;
            for ( i = 0; i < nHC;i++)
            {
//                 for (j = 0; j < nCount;)
//                 {
//                     HCDeep1[i] = ds.Tables[0].Rows[j][4].ToString();
//                     HCDeep2[i] = ds.Tables[0].Rows[j][5].ToString();
//                     HCDeep[i] = HCDeep1[i] + "--" + HCDeep2[i];
//                     j += int.Parse(ds.Tables[0].Rows[j][10].ToString());
//                 }

                DataSet ds1 = new DataSet();
                ZKImageInfo info1 = new ZKImageInfo();

                if (info1.GetZKImageInfoCount(zl_file_id, zk_code, i+1) == 0)
                {
                    HCDeep1[i] = "信息";
                    HCDeep2[i] = "缺失";
                } 
                else
                {
                    ds1 = info1.GetPicAllInfoWithSet(zl_file_id, zk_code, i+1);
                    HCDeep1[i] = ds1.Tables[0].Rows[0][8].ToString();
                    HCDeep2[i] = ds1.Tables[0].Rows[0][9].ToString();
/*                    HCDeep[i] = HCDeep1[i] + "--" + HCDeep2[i];*/
                }


            }

            DataSet result = new DataSet();
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("start", typeof(string)));
            dt.Columns.Add(new DataColumn("end", typeof(string)));
/*            dt.Columns.Add();*/

            for (i = 0; i < nHC;i++)
            {
                DataRow dr = dt.NewRow();
                dr[0] = HCDeep1[i];
                dr[1] = HCDeep2[i];

                dt.Rows.Add(dr);
            }

            result.Tables.Add(dt);
            return result;
        }

        #endregion


        #region 标本图片查找

        /// <summary>
        /// 钻孔图片查找
        /// 根据档号、钻孔编号和回次查找该回次下的所有图片
        /// 返回图片的名称
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet BBImgAdvancedQuery(string zl_file_id, string bb_code)
        {
            BBImageInfo ypinfo = new BBImageInfo();
            return ypinfo.GetPicNameWithSet(zl_file_id, bb_code);
        }

        #endregion
    }
}
